﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_ExtMailAdd]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_ExtMailAdd];
GO
CREATE PROCEDURE [dbo].[sproc_ExtMailAdd]
    (
     @Username            [nvarchar](50),
     @Email             [nvarchar](128),
     @ReadFlag         [bit],
     @FolderID         [int],
     @HeadersText         [text],
     @Subject             [nvarchar](200),
     @TextContent        [text],
     @HtmlContent            [text],
     @FromName         [nvarchar](50),
     @FromEmail         [nvarchar](300),
     @CcTo         [nvarchar](200),
     @BccTo         [nvarchar](200),
              @Replyto         [nvarchar](128),
              @SendDate         [datetime],
              @BodySize        [nvarchar](50) ,
     @Size                              [int],
     @MailID        [int] OUTPUT
)
AS 
    -- SET NOCOUNT to ON and no longer display the count message
    SET NOCOUNT ON
    
    DECLARE @CurrentError int
    -- start transaction, inserting into TabMailList
    BEGIN TRANSACTION
INSERT INTO TabExtMailList
     ( 
     [Username],
     [Email],
     [ReadFlag],
     [FolderID],
     [HeadersText],
      [Subject],
     [TextContent],
     [HtmlContent],
     [FromName],
     [FromEmail],
     [CcTo],
     [BccTo],
     [Replyto],
     [SendDate],
     [BodySize],
     [Size]
    ) 
 
VALUES 
    ( 
     @Username,
     @Email,
     @ReadFlag,
     @FolderID,
     @HeadersText,
     @Subject,
     @TextContent,
     @HtmlContent,
    @FromName,
     @FromEmail,
     @CcTo,
     @BccTo,
     @Replyto,
     @SendDate,
      @BodySize,
     @Size
     )
select @MailID=@@identity
  IF @CurrentError != 0
        BEGIN
               GOTO ERROR_HANDLER
        END
--insert  end
  
-- end of transaction
    COMMIT TRANSACTION
    -- Reset SET NOCOUNT to OFF
    SET NOCOUNT OFF
    -- return 0 to indicate success, otherwise the raised error will be returned
    RETURN 0
  ERROR_HANDLER:
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF
        RETURN @CurrentError